# -*- coding: utf-8 -*-
__author__ = 'Rui & Brazeta'

import rdflib
from rdflib.graph import ConjunctiveGraph, Namespace
import MySQLdb as mdb

rdflib.plugin.register('sparql', rdflib.query.Processor, 'rdfextras.sparql.processor', 'Processor')
rdflib.plugin.register('sparql', rdflib.query.Result, 'rdfextras.sparql.query', 'SPARQLQueryResult')


class RDF:
    def __init__(self):
        self._graph = ConjunctiveGraph()

    def readTriplesFileNt(self, filename):
        _graph = ConjunctiveGraph()
        _graph.parse(filename, format="nt")
        return _graph

    def readTriplesFileN3(self, filename):
        _graph = ConjunctiveGraph()
        _graph.parse(filename, format="n3")
        return _graph

    def readTriplesFileXML(self, filename):
        _graph = ConjunctiveGraph()
        _graph.parse(filename, format="xml")
        return _graph

    def saveTriplestoreToNT(self, filename, graph):
        of = open(filename + ".nt", "wb")
        #valorSerializacao = graph.serialize(format="nt")
        of.write(graph.serialize(format="nt"))
        of.close()

    def saveTriplestoreToN3(self, filename, graph):
        of = open(filename + ".n3", "wb")
        of.write(graph.serialize(format="n3"))
        of.close()

    def saveTriplestoreToXML(self, filename, graph):
        of = open(filename + ".xml", "wb")
        of.write(graph.serialize(format="xml"))
        of.close()

    def saveTriplestoreToDatabase(self, graph):

        con = mdb.connect('localhost', 'root', 'coiso123', 'rdflib_ws')
        cur = con.cursor()

        with con:
            for element in graph.triples((None, None, None)):
                query = str.format("INSERT INTO rdflib_triple (Subject, Predicate, Object) VALUES ('%s', '%s', '%s')" % (
                    element[0].__str__(), element[1].__str__(), element[2].__str__()))
                cur.execute(query)
            pass

        if con: con.close()

        pass


    def loadTriplestoreFromDatabase(self):

        graph = ConjunctiveGraph()

        con = mdb.connect('localhost', 'root', 'coiso123', 'rdflib_ws')

        instance = RDF()

        with con:

            cur = con.cursor()
            cur.execute("Select * From rdflib_triple")
            rows = cur.fetchall()

            for row in rows:
                sub = rdflib.URIRef(row[1])
                pred = rdflib.URIRef(row[2])

                if(instance.is_float_try(row[3])):
                    obj = rdflib.term.Literal(row[3], datatype=rdflib.term.URIRef(u'http://www.w3.org/2001/XMLSchema#double'))
                else:
                    obj = rdflib.Literal(row[3])

                graph.add((sub, pred, obj))

        if con: con.close()

        return graph

        pass


    def deleteAllDataFomDatabase(self):

        con = mdb.connect('localhost', 'root', 'coiso123', 'rdflib_ws')
        with con:
            cur = con.cursor()
            cur.execute("delete from rdflib_triple")
            pass
        con.commit()
        if con: con.close()
        pass

    def pesquisaJogadoresPorEquipa(self, equipa, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player WHERE { ?player sw:play_2011_2012 "' + equipa + '" . }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresMudaramEquipa(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player ?team_2011_2012 ?team_2012_2013 WHERE { ?player sw:play_2011_2012 ?team_2011_2012 . ?player sw:play_2012_2013 ?team_2012_2013 . FILTER( ?team_2011_2012 != ?team_2012_2013 ) }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresMediaPontosJogoMaiorQueValor(self, valor, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player ?ppg_2012_2013 WHERE { ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 . FILTER( ?ppg_2012_2013 > ' + valor + ' ) }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresMediaPontosEmIntervalo(self, valorMenor, valorMaior, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player ?ppg_2012_2013 WHERE { ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 . FILTER( ?ppg_2012_2013 > ' + valorMenor + ' && ?ppg_2012_2013 < ' + valorMaior + ' ) }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresAumentaramPontosEntreSeasons(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player ?ppg_2011_2012 ?ppg_2012_2013 WHERE { ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 . ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 . FILTER( ?ppg_2012_2013 > ?ppg_2011_2012 ) }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresDiminuiramPontosEntreSeasons(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = 'SELECT ?player ?ppg_2011_2012 ?ppg_2012_2013 WHERE { ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 . ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 . FILTER( ?ppg_2012_2013 < ?ppg_2011_2012 ) }'
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresBoaMudancaEquipa(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = """
        SELECT ?player ?team_2011_2012 ?team_2012_2013 ?ppg_2011_2012 ?ppg_2012_2013
        WHERE
        {
        ?player sw:play_2011_2012 ?team_2011_2012 .
        ?player sw:play_2012_2013 ?team_2012_2013 .
        ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 .
        ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 .
        FILTER( ?ppg_2012_2013 > ?ppg_2011_2012 && ?team_2011_2012 !=  ?team_2012_2013)
        }
        """
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def pesquisaJogadoresMaMudancaEquipa(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = """
        SELECT ?player ?team_2011_2012 ?team_2012_2013 ?ppg_2011_2012 ?ppg_2012_2013
        WHERE
        {
        ?player sw:play_2011_2012 ?team_2011_2012 .
        ?player sw:play_2012_2013 ?team_2012_2013 .
        ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 .
        ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 .
        FILTER( ?ppg_2012_2013 < ?ppg_2011_2012 && ?team_2011_2012 !=  ?team_2012_2013)
        }
        """
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def inferirBoaDecisaoMudancaEquipa(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = """CONSTRUCT { ?player <SemanticWebMSI://good_decision_changing_to> ?team_2012_2013 }
        WHERE
        {
        ?player sw:play_2011_2012 ?team_2011_2012 .
        ?player sw:play_2012_2013 ?team_2012_2013 .
        ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 .
        ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 .
        FILTER( ?ppg_2012_2013 > ?ppg_2011_2012 && ?team_2011_2012 != ?team_2012_2013)
        }"""
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def inferirMaDecisaoMudancaEquipa(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = """CONSTRUCT { ?player <SemanticWebMSI://bad_decision_changing_to> ?team_2012_2013 }
        WHERE
        {
        ?player sw:play_2011_2012 ?team_2011_2012 .
        ?player sw:play_2012_2013 ?team_2012_2013 .
        ?player sw:AVG_PPG_2011_2012 ?ppg_2011_2012 .
        ?player sw:AVG_PPG_2012_2013 ?ppg_2012_2013 .
        FILTER( ?ppg_2012_2013 < ?ppg_2011_2012 && ?team_2011_2012 != ?team_2012_2013)
        }"""
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass


    def inferirJogadoresConferenciaLeste(self, graph):

        swNameSpace = Namespace("SemanticWebMSI://")
        query = """CONSTRUCT { ?player <SemanticWebMSI://belong> \"ECAP_2011_2012\" }
        WHERE
        {
        ?player sw:play_2011_2012 ?team .
        FILTER(?team = "BOS" || ?team = "BKN" || ?team = "NY" || ?team = "PHI" || ?team = "TOR")
        }"""
        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass


    def inferirTopPlayers_2011_2012(self, graph):

        """
        Método utilizado para inferir quais os melhores jogadores de uma temporada (TOP Players)
        :param graph: grafo do tipo ConjuntiveGraph
        :return: resultado do método de query do ConjuntiveGraph
        """
        swNameSpace = Namespace("SemanticWebMSI://")
        query = """SELECT ?player
        WHERE
        {
        ?player sw:AVG_PPG_2011_2012 ?points_per_game_2011_2012 .
        }
        ORDER BY DESC (?points_per_game_2011_2012)
        LIMIT 10
        """

        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass


    def inferirJogadoresAscendenteCarreira(self, graph):

        """
        Método utilizado para inferir quais os melhores jogadores de uma temporada (TOP Players)
        :param graph: grafo do tipo ConjuntiveGraph
        :return: resultado do método de query do ConjuntiveGraph
        """
        swNameSpace = Namespace("SemanticWebMSI://")
        query = """CONSTRUCT { ?player <SemanticWebMSI://classified> \"Ascending_Player\" }
        WHERE
        {
        ?player sw:AVG_3PT_M_2012_2013 ?3PT_M_2012_2013 .
        ?player sw:AVG_FG_M_2012_2013 ?FG_M_2012_2013 .
        ?player sw:AVG_FT_M_2012_2013 ?FT_M_2012_2013 .
        ?player sw:AVG_PPG_2012_2013 ?PPG_2012_2013 .
        ?player sw:AVG_3PT_M_2011_2012 ?3PT_M_2011_2012 .
        ?player sw:AVG_FG_M_2011_2012 ?FG_M_2011_2012 .
        ?player sw:AVG_FT_M_2011_2012 ?FT_M_2011_2012 .
        ?player sw:AVG_PPG_2011_2012 ?PPG_2011_2012 .
        FILTER( ?3PT_M_2012_2013 > ?3PT_M_2011_2012 && ?FG_M_2012_2013 > ?FG_M_2011_2012 && ?FT_M_2012_2013 > ?FT_M_2011_2012 && ?PPG_2012_2013 > ?PPG_2011_2012 )
        }
        """

        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def inferirJogadoresFaseDescendenteCarreira(self, graph):

        """
        Método utilizado para inferir quais os melhores jogadores de uma temporada (TOP Players)
        :param graph: grafo do tipo ConjuntiveGraph
        :return: resultado do método de query do ConjuntiveGraph
        """
        swNameSpace = Namespace("SemanticWebMSI://")
        query = """CONSTRUCT { ?player <SemanticWebMSI://classified> \"Ascending_Player\" }
        WHERE
        {
        ?player sw:AVG_3PT_M_2012_2013 ?3PT_M_2012_2013 .
        ?player sw:AVG_FG_M_2012_2013 ?FG_M_2012_2013 .
        ?player sw:AVG_FT_M_2012_2013 ?FT_M_2012_2013 .
        ?player sw:AVG_PPG_2012_2013 ?PPG_2012_2013 .
        ?player sw:AVG_3PT_M_2011_2012 ?3PT_M_2011_2012 .
        ?player sw:AVG_FG_M_2011_2012 ?FG_M_2011_2012 .
        ?player sw:AVG_FT_M_2011_2012 ?FT_M_2011_2012 .
        ?player sw:AVG_PPG_2011_2012 ?PPG_2011_2012 .
        FILTER( ?3PT_M_2012_2013 < ?3PT_M_2011_2012 && ?FG_M_2012_2013 < ?FG_M_2011_2012 && ?FT_M_2012_2013 < ?FT_M_2011_2012 && ?PPG_2012_2013 < ?PPG_2011_2012 )
        }
        """

        results = graph.query(query, initNs={'sw': swNameSpace})
        return results
        pass

    def is_float_try(self, str):
        try:
            float(str)
            return True
        except ValueError:
            return False